******************************************

*			MERGING IN DATA FROM		  *
*			    Near Tables			      *

*******************************************
clear
clear matrix
clear mata
set maxvar 10000
set more off
*cd "C:\Users\mrh105\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Compiling Near Table" /*PC*/
cd "C:\Users\maxha\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Compiling Near Table" /*Home PC*/ 

import dbase Building_Area.dbf
rename SqM FootPrint_Area_SqM
keep OBJECTID FootPrint_Area_SqM
rename OBJECTID bldg_id
save Building_Area_SqM.dta, replace

clear
*use "C:\Users\mrh105\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Building Data\WC_TaxRoll_GIS_BackMerge1.dta" /*PC*/
use "C:\Users\maxha\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Building Data\WC_TaxRoll_GIS_BackMerge1.dta" /*Home PC*/
merge 1:1 bldg_id using Building_Area_SqM.dta
drop OBJECTID PARID PIN _merge
save WC_TaxRoll_GIS_BackMerge1_short.dta, replace

clear
*use "C:\Users\mrh105\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Well Data\DEP_DCNR_COMBINED_BackMerge1.dta" /*PC*/
use "C:\Users\maxha\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data\DEP_DCNR_COMBINED_BackMerge1.dta" /*Home PC*/
drop county latitudedec longitudedec reporting_production retroactive date_plugg_year permit_dat_year spud_date_year last_produ_report_year first_produ_report_year maxyear minyear_decade operatorname wellstatus wellstatus_short year_since event stimulationcompletiondate_year pre1955 deepestproducingdepthft totalmeasureddepthfromft totalmeasureddepthtoft
save DEP_DCNR_COMBINED_BackMerge1_short.dta, replace
	
clear
import dbase WC_Wells_Build_NearTable.dbf
drop OBJECTID
rename IN_FID well_id
rename NEAR_FID bldg_id
merge m:1 bldg_id using WC_TaxRoll_GIS_BackMerge1_short.dta
drop _merge bldg_id
drop if NEAR_RANK==.
rename NEAR_DIST near_building_dist
rename type near_building_type
reshape wide near_building_dist near_building_type YRBLT SFLA RMTOT RMBED FIXBATH central_ac FootPrint_Area_SqM, i(well_id) j(NEAR_RANK)
save WC_Wells_Build_Withdata.dta, replace

clear
import dbase WC_Wells_Parcels_NearTable.dbf
drop OBJECTID
rename IN_FID well_id
rename NEAR_FID parcel_id
rename NEAR_DIST near_parcel_dist
bysort well_id: gen NEAR_RANK2 = [_n]
drop NEAR_RANK parcel_id
reshape wide near_parcel_dist, i(well_id) j(NEAR_RANK2)
save WC_Wells_Parcels_Withdata.dta, replace

clear
import dbase WC_Wells_Lots_NearTable.dbf
drop OBJECTID
rename IN_FID well_id
rename NEAR_FID lot_id
rename NEAR_DIST near_lot_dist
bysort well_id: gen NEAR_RANK2 = [_n]
drop NEAR_RANK lot_id NEAR_ANGLE
reshape wide near_lot_dist, i(well_id) j(NEAR_RANK2)
save WC_Wells_Lots_Withdata.dta, replace

clear
import dbase WC_Wells_Wells_NearTable.dbf
drop OBJECTID
rename IN_FID well_id2
rename NEAR_FID well_id
merge m:1 well_id using DEP_DCNR_COMBINED_BackMerge1_short.dta
drop _merge well_id
drop if NEAR_RANK==.
rename well_id2 well_id
rename NEAR_DIST near_well_dist
bysort well_id: gen NEAR_RANK2 = [_n]
drop NEAR_RANK
reshape wide near_well_dist permit_num welltype plugged never_report minyear, i(well_id) j(NEAR_RANK2)
save WC_Wells_Wells_Withdata.dta, replace

clear
use WC_Wells_Build_Withdata.dta
merge 1:1 well_id using WC_Wells_Wells_Withdata.dta
drop _merge
merge 1:1 well_id using WC_Wells_Parcels_Withdata.dta
drop _merge
merge 1:1 well_id using WC_Wells_Lots_Withdata.dta
drop _merge
save WC_Wells_Wells_Build_Withdata.dta, replace
 
clear
import dbase WC_Wells_NearestFields.dbf
drop OBJECTID latitudede longituded
rename SdvOutput0 slope
rename SdvOutput1 dwelling_with_basement
rename SdvOutpu_1 dwelling_without_basement
merge 1:1 well_id using WC_Wells_Wells_Build_Withdata.dta
sort well_id
drop _merge
*merge 1:1 well_id using "C:\Users\mrh105\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Well Data\DEP_DCNR_COMBINED_BackMerge1.dta" /*PC*/
merge 1:1 well_id using "C:\Users\maxha\Box\EDWIN_DEP_Well_Data\Washington_County_Project\Final Code\Well Data\DEP_DCNR_COMBINED_BackMerge1.dta"  /*Home PC*/
drop _merge
save WC_Wells_Soil_Near_Withdata.dta,replace

clear	
import dbase Wells_Fields.dbf
rename Disc_Yr Field_Discovery_Yr
keep Field_Name well_id Field_Discovery_Yr
merge 1:1 well_id using WC_Wells_Soil_Near_Withdata.dta
drop _merge
save WC_Wells_Soil_Near_Withdata_Fields.dta, replace

clear	
import dbase Wells_Pools.dbf
rename Field_Name Field_Name_From_Pools
merge 1:1 well_id using WC_Wells_Soil_Near_Withdata_Fields.dta
drop _merge
tostring Pool_ID, replace
replace Field_Name_From_Pools= Field_Name_From_Pools+Pool_ID
replace Field_Name_From_Pools="NOT IN POOL " + Field_Name if Field_Name_From_Pools=="0"
rename Field_Name_From_Pools Pool_Name
encode Pool_Name, generate(Pool_num)
order well_id permit_num
save WC_Wells_Soil_Near_Withdata_Fields_Pools.dta,replace

	*Cleaning Up*
	erase WC_Wells_Soil_Near_Withdata_Fields.dta
	erase WC_Wells_Soil_Near_Withdata.dta
	erase WC_Wells_Wells_Build_Withdata.dta
	erase WC_Wells_Lots_Withdata.dta
	erase WC_Wells_Wells_Withdata.dta
	erase WC_Wells_Parcels_Withdata.dta
	erase WC_Wells_Build_Withdata.dta
	erase DEP_DCNR_COMBINED_BackMerge1_short.dta
	*erase WC_TaxRoll_GIS_BackMerge1_short.dta
	*erase WC_Wells_Soil_Near_Withdata_Fields_Pools.dta
	*erase Building_Area_SqM.dta
